package edu.upenn.cis.cis550.hw3.server;

import java.sql.Array;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;

import com.google.gwt.user.server.rpc.RemoteServiceServlet;

import edu.upenn.cis.cis550.hw3.client.QueryTables;
import edu.upenn.cis.cis550.hw3.server.RelationBuilder;
import edu.upenn.cis.cis550.hw3.shared.Relation;
import edu.upenn.cis.cis550.hw3.shared.Tuple;

/**
 * This class operates on the server (not the browser) and does a computation.
 * Its output will be returned to the browser.
 * 
 * @author zives
 * 
 */
public class QueryTablesImpl extends RemoteServiceServlet implements
        QueryTables {
    Connection connection;

    private static final long serialVersionUID = 1L;

    /**
     * Constructor -- initialize our source tables and the object
     */
    public QueryTablesImpl() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String serverName = "fling.seas.upenn.edu:3306";
            String database = "silviamo";
            String url = "jdbc:mysql://" + serverName + "/" + database;
            String user = "silviamo";
            String password = "dbproject";

            connection = DriverManager.getConnection(url, user, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.exit(1);
        } catch (SQLException e) {
            e.printStackTrace();
            System.exit(1);
        }
    }

    /**
     * Returns true if the password and username combination inputed are valid.
     * False otherwise.
     */
    public Boolean validatePassword(String username, String pass)
            throws IllegalArgumentException {
        try {
            Statement statement = connection.createStatement();

            if (statement.execute("select * from Login where Login.userID = \""
                    + username + "\" AND Login.password" + " = \"" + pass
                    + "\"")) {

                ResultSet rs = statement.getResultSet();
                if (rs.first())
                    return true;
                else
                    return false;
            }
        } catch (SQLException e) {

            e.printStackTrace();
        }
        return false;
    }

    public Boolean isItStudent(String userid) throws IllegalArgumentException {
        try {
            Statement statement = connection.createStatement();
            if (statement
                    .execute("select S.studentID from Student S where S.studentID = "
                            + userid)) {
                ResultSet rs = statement.getResultSet();
                if (rs.first()) {
                    return true;
                } else
                    return false;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public Boolean isItProfessor(String userid) throws IllegalArgumentException {
        try {
            Statement statement = connection.createStatement();
            if (statement
                    .execute("select P.professorID from Professor P where P.professorID = "
                            + userid)) {
                ResultSet rs = statement.getResultSet();
                if (rs.first()) {
                    return true;
                } else
                    return false;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * Next 6 methods should be called to get initial info for user home page
     */
    public Relation getUserInfo(String userid) throws IllegalArgumentException {
        try {
            Statement statement = connection.createStatement();
            if (statement
                    .execute("select firstName, lastName, dateBirth, email, gender from User where userID = "
                            + userid)) {
                ResultSet rs = statement.getResultSet();
                return RelationBuilder.createRelation("UserInformation", rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public Relation getStudentInfo(String userid)
            throws IllegalArgumentException {
        try {
            Statement statement = connection.createStatement();
            if (statement
                    .execute("select year, major, GPA, academicInstitution from Student where studentID = "
                            + userid)) {
                ResultSet rs = statement.getResultSet();
                return RelationBuilder
                        .createRelation("Student Information", rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public Relation getProfessorInfo(String userid)
            throws IllegalArgumentException {
        try {
            Statement statement = connection.createStatement();
            if (statement
                    .execute("select roomNumber, researchArea, title, academicInstitution from Professor where professorID = "
                            + userid)) {
                ResultSet rs = statement.getResultSet();
                return RelationBuilder.createRelation("Professor Information",
                        rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public Relation getRecentUpdates(String userid)
            throws IllegalArgumentException {
        try {
            Statement statement = connection.createStatement();

            if (statement
                    .execute("select P.photoID, P.photoTime, P.url, F.friendID, U.firstName, U.lastName from Photo P, "
                            + "Friend F, User U where F.userID = "
                            + userid
                            + " and P.userID = F.friendID and P.userID=U.userID order by photoTime desc limit 10")) {
                ResultSet rs = statement.getResultSet();
                return RelationBuilder.createRelation("Recent Updates", rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * To get Photo details for
     */
    public Relation getPhotoDetails(String userid)
            throws IllegalArgumentException {
        try {
            Statement statement = connection.createStatement();

            if (statement
                    .execute("select P.url, P.description, P.photoTime, avg(R.score) as average, T.tag, "
                            + "C.comment, C.commentTime from Photo P, Rating R, Tag T, Comment C where "
                            + "P.photoID=R.photoID AND P.photoID=T.photoID AND P.photoID=C.photoID and "
                            + "P.userID=" + userid + "group by P.photoID")) {
                ResultSet rs = statement.getResultSet();
                return RelationBuilder.createRelation("Photo Details", rs);
            }
        } catch (SQLException e) {

            e.printStackTrace();
        }
        return null;
    }

    public void createViewVisiblePhotos(String userID) {
        try {
            Statement statement = connection.createStatement();
            statement
                    .execute("create or replace view VisiblePhotos as select * from Photo "
                            + " where (visibility = 1 and photoID in (select photoID from VisibUsers where userID = "
                            + userID
                            + "))"
                            + " or visibility = 0 or userID ="
                            + userID
                            + " or (visibility = 2 and photoID in (select V.photoID from VisibCircles V, Friend F, Circle C "
                            + " where V.circleID = C.circleID and C.name = F.circleName and F.friendID = "
                            + userID + "));");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * Returns top scoring photos to user home page by computing a query
     */
    public Relation getTopScoringPhotos(String userid)
            throws IllegalArgumentException {
        double maxScore = 12.5;
        createViewVisiblePhotos(userid);
        createViewFromFriends(userid);
        createViewRatedByFriends(userid);
        createViewAvgScore();
        try {
            Statement statement = connection.createStatement();
            if (statement
                    .execute("select FF.photoID, FF.url, (AV.aveScore + FF.scoreFromFriend + FR.ratedScore) as finalScore, "
                            + "((AV.aveScore + FF.scoreFromFriend + FR.ratedScore)/"
                            + maxScore
                            + ") as percentScore from AV, FR, FF "
                            + "where FF.photoID = AV.photoID and FF.photoID = FR.photoID order by finalScore desc")) {
                ResultSet rs = statement.getResultSet();
                Relation r = RelationBuilder.createRelation(
                        "Top Scoring Photos", rs);
                return r;
            }
        } catch (SQLException e) {

            e.printStackTrace();
        }
        return null;
    }

    /**
     * Methods for sorting photos.
     */
    public void createViewRatedByFriends(String userid) {
        try {
            Statement statement = connection.createStatement();
            statement
                    .execute("create or replace view FR(photoID,url,ratedScore) as "
                            + "select P.photoID, P.url, if(P.photoID IN (select P3.photoID from Photo P3, Rating R, Friend F "
                            + "where P3.userID = "
                            + userid
                            + " and F.userID = "
                            + userid
                            + " and R.photoID = P3.photoID and "
                            + "F.friendID = R.userID),(select avg(R.score) from Photo P3, Rating R, Friend F where "
                            + "P3.userID = "
                            + userid
                            + " and F.userID = "
                            + userid
                            + " and R.photoID = P3.photoID and "
                            + "F.friendID = R.userID and P3.photoID = P.photoID),0) as Score2 from VisiblePhotos P;");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void createViewFromFriends(String userid) {
        int ifItIsFromFriend = 1;
        try {
            Statement statement = connection.createStatement();

            statement
                    .execute("create or replace view FF(photoID,url,scoreFromFriend) as "
                            + "select P.photoID, P.url, if(P.photoID IN (select P2.photoID from Photo P2, Friend F where "
                            + "F.friendID = P2.userID and F.userID = "
                            + userid
                            + "),"
                            + ifItIsFromFriend
                            + ",0) as Score1 "
                            + "from VisiblePhotos P;");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void createViewAvgScore() {
        try {
            Statement statement = connection.createStatement();

            statement
                    .execute("create or replace view AV(photoID,url,aveScore) as select P.photoID, P.url, avg(R.score) as Score3 "
                            + "from VisiblePhotos P, Rating R where R.photoID = P.photoID group by P.photoID union select P.photoID, P.url, "
                            + "0 as Score3 from VisiblePhotos P where P.photoID not in (select P.photoID from VisiblePhotos P, Rating R where "
                            + "R.photoID = P.photoID group by P.photoID);");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * Methods for browsing photos capability Helper method to update keywords
     * when new tag is inserted for browsing photos
     */
    public Relation getSearchedPhotos(String keywords, String userid) {
        createViewVisiblePhotos(userid);
        String[] a1 = keywords.trim().split(" ", 0);
        StringBuilder query = new StringBuilder();

        query.append("select V.url from VisiblePhotos V, TagsKeywords T, Keywords K "
                + "where V.photoID = T.photoID" + " and T.wordID = K.wordID ");
        for (String s : a1) {
            query.append(" and K.word = \"" + s + "\"");
        }
        try {
            Statement statement = connection.createStatement();
            statement.execute(query.toString());
            ResultSet rs = statement.getResultSet();
            return RelationBuilder.createRelation("Searched Photos", rs);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public Relation getPhotoTags(String photoid)
            throws IllegalArgumentException {
        try {
            Statement statement = connection.createStatement();
            if (statement
                    .execute("select T.tag from Photo P, Tag T where P.photoID = "
                            + photoid + " and T.photoID = P.photoID")) {
                ResultSet rs = statement.getResultSet();
                return RelationBuilder.createRelation("Photo Tags", rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * Methods for browsing friends capability
     */
    public Relation getFriends(int userid) {
        try {
            Statement statement = connection.createStatement();
            statement
                    .execute("create or replace view FriendIDs(friendID, FirstName, LastName) as SELECT F.friendID, U.FirstName,"
                            + "U.LastName FROM Friend F, User U WHERE F.userID="
                            + userid + " and F.friendID = U.userID");
            statement.execute("select * from FriendIDs");
            ResultSet rs = statement.getResultSet();
            return RelationBuilder.createRelation("Friends of User", rs);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return null;
    }

    public Relation getAdvisorInfo(String userid)
            throws IllegalArgumentException {
        try {
            Statement statement = connection.createStatement();

            if (statement
                    .execute("select p.professorID as advisor, p.roomNumber, p.researchArea, p.title, p.academicInstitution, a.studentID as advisee, a.yearsAdvised from Professor p, Advises a, Student s where p.professorID=a.professorID and s.studentID=a.studentID and s.studentID="
                            + userid)) {
                ResultSet rs = statement.getResultSet();
                return RelationBuilder
                        .createRelation("Advisor Information", rs);
            }
        } catch (SQLException e) {

            e.printStackTrace();
        }
        return null;
    }

    public Relation getAdviseeInfo(String userid)
            throws IllegalArgumentException {
        try {
            Statement statement = connection.createStatement();

            if (statement
                    .execute("select s.studentID as advisee, s.year, s.major, s.GPA, s.academicInstitution, a.professorID as adivsor, a.yearsAdvised from Student s, Advises a, Professor p where s.studentID=a.studentID and a.professorID=p.professorID and p.professorID="
                            + userid)) {
                ResultSet rs = statement.getResultSet();
                return RelationBuilder
                        .createRelation("Advisee Information", rs);
            }
        } catch (SQLException e) {

            e.printStackTrace();
        }
        return null;
    }

    public Relation getProfessors() {
        try {
            Statement statement = connection.createStatement();

            if (statement
                    .execute("select U.firstName, U.lastName, P.professorID from User U, Professor P where U.userID=P.professorID")) {
                ResultSet rs = statement.getResultSet();
                return RelationBuilder
                        .createRelation("All professors", rs);
            }
        } catch (SQLException e) {

            e.printStackTrace();
        }
        return null;

    }

    /**
     * Friend Information for a given User
     */
    public Relation getFriendsInfo(String userid)
            throws IllegalArgumentException {
        try {
            Statement statement = connection.createStatement();

            if (statement
                    .execute("Select * from User where userID IN (Select friendID from Friend where Friend.userID="
                            + userid + ")")) {
                ResultSet rs = statement.getResultSet();
                return RelationBuilder
                        .createRelation("Friends Information", rs);
            }
        } catch (SQLException e) {

            e.printStackTrace();
        }
        return null;
    }

    /**
     * Friend Information for a given User
     */
    public Relation getCirclesInfo(String userid)
            throws IllegalArgumentException {
        try {
            Statement statement = connection.createStatement();

            if (statement
                    .execute("Select circleID, name from Circle where userID=\""
                            + userid + "\"")) {
                ResultSet rs = statement.getResultSet();
                return RelationBuilder
                        .createRelation("Friends Information", rs);
            }
        } catch (SQLException e) {

            e.printStackTrace();
        }
        return null;
    }

    public String getFriendsVisualizerXML(int userid) {
        StringBuilder xml = new StringBuilder();
        Iterator<Tuple> i = getFriends(userid).iterator();

        // xml.append("<photodb>");
        xml.append("<user>");
        xml.append("<id>");
        xml.append(userid);
        xml.append("</id>");
        xml.append("<name>ME!</name>");
        xml.append("<circle><name>Dummy</name>");
        while (i.hasNext()) {
            Tuple t = i.next();
            xml.append("<containsFriend>");
            xml.append("<id>");
            xml.append(t.getValue("friendID"));
            xml.append("</id>");
            xml.append("<name>");
            xml.append(t.getValue("FirstName").toString() + " " + t.getValue("LastName").toString());
            xml.append("</name>");
            xml.append("</containsFriend>");
        }
        xml.append("</circle>");
        xml.append("</user>");
        // xml.append("</photodb>");
        System.out.println(xml.toString());
        return xml.toString();
    }

    public ArrayList<String> getAllTags() {
        ArrayList<String> result = new ArrayList<String>();
        try {
            Statement statement = connection.createStatement();

            if (statement.execute("Select tag from Tag")) {
                ResultSet rs = statement.getResultSet();
                while (rs.next()) {
                    result.add(rs.getString(1));
                }
            }
            return result;
        } catch (SQLException e) {

            e.printStackTrace();
        }
        return null;

    }

    public String addFriend(int userID, int friendID, String circleName) {
        System.out.println("here");
        String delim = "\",\"";
        StringBuilder query = new StringBuilder();
        query.append("INSERT INTO Friend VALUES (\"");
        query.append(userID);
        query.append(delim);
        query.append(friendID);
        query.append(delim);
        query.append(circleName);
        query.append("\");");
        String queryString = query.toString();
        System.out.println(queryString);
        try {
            Statement statement = connection.createStatement();
            statement.execute(queryString);
            return "Sucess! New friend added";
        } catch (SQLException e) {
            return e.getMessage();
        }

    }
}